package com.example.demo.util;

import java.sql.*;

public class JdbcDemo {

    // 1. 调用存储过程：批量更新订单状态
    public static void batchUpdateOrderStatus(String oldStatus, String newStatus, String beforeDate) throws Exception {
        try (Connection conn = DBUtil.getConnection();
             CallableStatement cs = conn.prepareCall("{call batch_update_order_status(?, ?, ?)}")) {
            cs.setString(1, oldStatus);
            cs.setString(2, newStatus);
            cs.setString(3, beforeDate);
            cs.execute();
            System.out.println("批量订单状态更新完成。");
        }
    }

    // 2. 调用存储过程：用户注册量统计
    public static void countUserRegistration(String startDate, String endDate) throws Exception {
        try (Connection conn = DBUtil.getConnection();
             CallableStatement cs = conn.prepareCall("{call count_user_registration(?, ?)}")) {
            cs.setString(1, startDate);
            cs.setString(2, endDate);
            ResultSet rs = cs.executeQuery();
            System.out.println("用户类型\t注册数量");
            while (rs.next()) {
                System.out.println(rs.getString("user_type") + "\t" + rs.getInt("reg_count"));
            }
        }
    }

    // 3. 调用存储函数：根据用户ID返回用户类型
    public static String getUserType(long userId) throws Exception {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT get_user_type(?) AS user_type")) {
            ps.setLong(1, userId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getString("user_type");
            }
        }
        return null;
    }

    // 4. 调用存储函数：根据订单ID返回订单总金额
    public static double getOrderTotal(long orderId) throws Exception {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT get_order_total(?) AS total")) {
            ps.setLong(1, orderId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getDouble("total");
            }
        }
        return 0;
    }

    // 5. 执行DML：用户信息更新
    public static void updateUser(long userId, String newUsername, String userType) throws Exception {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("UPDATE users SET username=?, user_type=? WHERE id=?")) {
            ps.setString(1, newUsername);
            ps.setString(2, userType);
            ps.setLong(3, userId);
            int rows = ps.executeUpdate();
            System.out.println("更新用户数量：" + rows);
        }
    }

    // 6. 执行DML：删除订单
    public static void deleteOrder(long orderId) throws Exception {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("DELETE FROM orders WHERE id=?")) {
            ps.setLong(1, orderId);
            int rows = ps.executeUpdate();
            System.out.println("删除订单数量：" + rows);
        }
    }

    // 7. 报表查询：每月订单总数和总金额
    public static void monthlyOrderReport() throws Exception {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, COUNT(*) AS order_count, SUM(total_amount) AS total_amount " +
                             "FROM orders GROUP BY DATE_FORMAT(create_time, '%Y-%m') ORDER BY month DESC")) {
            ResultSet rs = ps.executeQuery();
            System.out.println("月份\t订单数\t总金额");
            while (rs.next()) {
                System.out.println(rs.getString("month") + "\t" + rs.getInt("order_count") + "\t" + rs.getDouble("total_amount"));
            }
        }
    }

    // 8. 查询视图：每日各类型用户注册统计
    public static void queryUserRegistrationView() throws Exception {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT * FROM v_daily_user_registration")) {
            ResultSet rs = ps.executeQuery();
            System.out.println("日期\t用户类型\t注册数");
            while (rs.next()) {
                System.out.println(rs.getString("reg_date") + "\t" + rs.getString("user_type") + "\t" + rs.getInt("reg_count"));
            }
        }
    }

    // 9. 查询视图：订单详细信息
    public static void queryOrderDetailView() throws Exception {
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT * FROM v_order_detail")) {
            ResultSet rs = ps.executeQuery();
            System.out.println("订单号\t服务类型\t用户名\t订单状态\t金额\t下单时间");
            while (rs.next()) {
                System.out.println(rs.getString("order_no") + "\t" +
                        rs.getString("service_type") + "\t" +
                        rs.getString("username") + "\t" +
                        rs.getString("status") + "\t" +
                        rs.getDouble("total_amount") + "\t" +
                        rs.getString("create_time"));
            }
        }
    }

    // main方法测试
    public static void main(String[] args) throws Exception {
        // 示例调用
        batchUpdateOrderStatus("PENDING", "CANCELLED", "2024-06-01 00:00:00");
        countUserRegistration("2024-06-01 00:00:00", "2024-06-30 23:59:59");
        System.out.println("用户类型：" + getUserType(1));
        System.out.println("订单总金额：" + getOrderTotal(1001));
        updateUser(1, "新用户名", "user");
        deleteOrder(1001);
        monthlyOrderReport();
        queryUserRegistrationView();
        queryOrderDetailView();
    }
} 